/**
 * 
 */
package rai.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import rai.dto.AppDTO;
import rai.dto.DatabaseDTO;
import rai.dto.FrwDTO;
import rai.dto.LangDTO;
import rai.dto.TechDTO;
import rai.form.AddPrjFormAdmin;

/**
 * Database operation for Add Actions of Admin.
 * @author Vu
 *
 */
public class AddActionDAO {
    /** Log property. */
    private static Logger log = Logger.getLogger("EditTakenPrjInfoDAO.start");
    /**
     * get belonged Lang.
     * @param prjID
     * @param type
     * @return list Lang
     */
    public List<LangDTO> getLang(int prjID, int type) {
        LangDTO dto;
        final List<LangDTO> result = new ArrayList<LangDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            // query command
            if(type == 2) {
                query = "select distinct LangID,Name from language where LangID in "
                    + "(select LangID from course_language where CourseID = "
                    + prjID + ")";

            } else {
                query = "select distinct LangID,Name from language where LangID in "
                        + "(select LangID from project_language where ProjectID = "
                        + prjID + ")";
            }
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);
            // pStat.setInt(1, CompanyID);
            // pStat.setString(2, CompName);
            // execute query
            rs = pStat.executeQuery();
            // get result
            
            while (rs.next()) {
                dto = new LangDTO();
                dto.setId(rs.getInt("LangID"));
                dto.setSname(rs.getString("Name"));
                result.add(dto);
            }

           
        } catch (final Exception sqlEx) {
            log.error("AddActionDAO.getLang " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("AddActionDAO.getLang " + sqlEx);
            }
        }

        return result;
    }
    /**
     * get belonged Frw.
     * @param prjID
     * @param type
     * @return lst of Frw
     */
    public List<FrwDTO> getFrw(int prjID, int type) {
        FrwDTO dto;
        final List<FrwDTO> result = new ArrayList<FrwDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            // query command
            if(type == 2) {
                query = "select distinct FrameID,Name from framework where FrameID in "
                    + "(select FrameID from course_framework where CourseID = "
                    + prjID + ")";

            } else {
                query = "select distinct FrameID,Name from framework where FrameID in "
                        + "(select FrameID from project_framework where ProjectID = "
                        + prjID + ")";
            }
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);
            // pStat.setInt(1, CompanyID);
            // pStat.setString(2, CompName);
            // execute query
            rs = pStat.executeQuery();
            // get result
            
            while (rs.next()) {
                dto = new FrwDTO();
                dto.setId(rs.getInt("FrameID"));
                dto.setSname(rs.getString("Name"));
                result.add(dto);
            }

           
        } catch (final Exception sqlEx) {
            log.error("AddActionDAO.getLang " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("AddActionDAO.getLang " + sqlEx);
            }
        }

        return result;
    }
    /**
     * get belonged App.
     * @param prjID
     * @param type
     * @return lst of App
     */
    public List<AppDTO> getApp(int prjID, int type) {
        AppDTO dto;
        final List<AppDTO> result = new ArrayList<AppDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            // query command
            if(type == 2) {
                query = "select distinct AppID,Name from application where AppID in "
                    + "(select AppID from course_aplication where CourseID = "
                    + prjID + ")";

            } else {
                query = "select distinct AppID,Name from application where AppID in "
                        + "(select AppID from project_application where ProjectID = "
                        + prjID + ")";
            }
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);
            // pStat.setInt(1, CompanyID);
            // pStat.setString(2, CompName);
            // execute query
            rs = pStat.executeQuery();
            // get result
            
            while (rs.next()) {
                dto = new AppDTO();
                dto.setId(rs.getInt("AppID"));
                dto.setSname(rs.getString("Name"));
                result.add(dto);
            }

           
        } catch (final Exception sqlEx) {
            log.error("AddActionDAO.getLang " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("AddActionDAO.getLang " + sqlEx);
            }
        }

        return result;
    }
    /**
     * get belonged App.
     * @param prjID
     * @param type
     * @return lst of App
     */
    public List<TechDTO> getTech(int prjID, int type) {
        TechDTO dto;
        final List<TechDTO> result = new ArrayList<TechDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            // query command
            if(type == 2) {
                query = "select distinct TechID,Name from technology where TechID in "
                    + "(select TechID from course_technology where CourseID = "
                    + prjID + ")";

            } else {
                query = "select distinct TechID,Name from technology where TechID in "
                        + "(select TechID from project_technology where ProjectID = "
                        + prjID + ")";
            }
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);
            // pStat.setInt(1, CompanyID);
            // pStat.setString(2, CompName);
            // execute query
            rs = pStat.executeQuery();
            // get result
            
            while (rs.next()) {
                dto = new TechDTO();
                dto.setId(rs.getInt("AppID"));
                dto.setSname(rs.getString("Name"));
                result.add(dto);
            }

           
        } catch (final Exception sqlEx) {
            log.error("AddActionDAO.getLang " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("AddActionDAO.getLang " + sqlEx);
            }
        }

        return result;
    }
    /**
     * get belonged Database.
     * @param prjID
     * @param type
     * @return lst of Database
     */
    public List<DatabaseDTO> getDatabase(int prjID, int type) {
        DatabaseDTO dto;
        final List<DatabaseDTO> result = new ArrayList<DatabaseDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            // query command
            if(type == 2) {
                query = "select distinct DatabaseID,Name from database where DatabaseID in "
                    + "(select DatabaseID from course_database where CourseID = "
                    + prjID + ")";

            } else {
                query = "select distinct DatabaseID,Name from database where DatabaseID in "
                        + "(select DatabaseID from project_database where ProjectID = "
                        + prjID + ")";
            }
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);
            // pStat.setInt(1, CompanyID);
            // pStat.setString(2, CompName);
            // execute query
            rs = pStat.executeQuery();
            // get result
            
            while (rs.next()) {
                dto = new DatabaseDTO();
                dto.setId(rs.getInt("DatabaseID"));
                dto.setSname(rs.getString("Name"));
                result.add(dto);
            }

           
        } catch (final Exception sqlEx) {
            log.error("AddActionDAO.getLang " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("AddActionDAO.getLang " + sqlEx);
            }
        }

        return result;
    }
    /**
     * Add New Project.
     * @param addPrj
     * @return error msg
     */
    public String addPrjAdmin(AddPrjFormAdmin addPrj) {
        String error="";
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;
        try {
            query = "select * from project p where p.ProjectName = " + addPrj.getPrjName();
            
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            rs=pStat.executeQuery(query);
            
            if(!rs.next()){
                int prjID = 0;
                query = "insert into project "
                		+ "(ProjectName,PeriodFrom,PeriodTo,Size,DepartID,IsDeleted,Description) "
                		+ "value('" + addPrj.getPrjName() + "','" + addPrj.getPeriodFrom() + "','" 
                		+ "','" + addPrj.getPeriodTo() + "','" + addPrj.getDep() + "',0x00,'" 
                		+ addPrj.getTxtPrjDes()+ "')";
                
                pStat = conn.prepareStatement(query);
                pStat.executeUpdate(query);
                
                query = "select ProjectID from project where ProjectName = "+ addPrj.getPrjName();
            
                pStat = conn.prepareStatement(query);
                rs = pStat.executeQuery(query);
                while(rs.next()) {
                    prjID = rs.getInt("ProjectID");
                }
                String txtapp = addPrj.getApptxt();
                if (!txtapp.equals("") && txtapp != null) {
                    final String[] temp = txtapp.split("\\;");
                    for (int i = 0; i < temp.length; i++) {
                        query = "insert into project_application "
                            + "value('" + prjID + "','" + Integer.parseInt(temp[i]) + "',0x00)";
                    
                        pStat = conn.prepareStatement(query);
                        pStat.executeUpdate(query);
                    }
                }
                
                String txtlang = addPrj.getLangtxt();
                if (!txtlang.equals("") && txtlang != null) {
                    final String[] temp = txtlang.split("\\;");
                    for (int i = 0; i < temp.length; i++) {
                        query = "insert into project_lang "
                            + "value('" + prjID + "','" + Integer.parseInt(temp[i]) + "',0x00)";
                    
                        pStat = conn.prepareStatement(query);
                        pStat.executeUpdate(query);
                    }
                }
                String txttech = addPrj.getTechtxt();
                if (!txttech.equals("") && txttech != null) {
                    final String[] temp = txttech.split("\\;");
                    for (int i = 0; i < temp.length; i++) {
                        query = "insert into project_technology "
                            + "value('" + prjID + "','" + Integer.parseInt(temp[i]) + "',0x00)";
                    
                        pStat = conn.prepareStatement(query);
                        pStat.executeUpdate(query);
                    }
                }
                
                String txtFrw = addPrj.getFrwtxt();
                if (!txtFrw.equals("") && txtFrw != null) {
                    final String[] temp = txtFrw.split("\\;");
                    for (int i = 0; i < temp.length; i++) {
                        query = "insert into project_framework "
                            + "value('" + prjID + "','" + Integer.parseInt(temp[i]) + "',0x00)";
                    
                        pStat = conn.prepareStatement(query);
                        pStat.executeUpdate(query);
                    }
                }
                String txtDatabase = addPrj.getTxtDatabase();
                if (!txtDatabase.equals("") && txtDatabase != null) {
                    final String[] temp = txtDatabase.split("\\;");
                    for (int i = 0; i < temp.length; i++) {
                        query = "insert into project_database "
                            + "value('" + prjID + "','" + Integer.parseInt(temp[i]) + "',0x00)";
                    
                        pStat = conn.prepareStatement(query);
                        pStat.executeUpdate(query);
                    }
                }
            } else {
                error = "Project Name Is Duplicated!!!";
            }
        } catch (final Exception sqlEx) {
            log.error("EditTakenProjectDAO.addPrj" + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("EditTakenProjectDAO.addPrj " + sqlEx);
            }
        }
        log.debug("EditTakenProjectDAO.addPrj.end");
        error = "Information Add Successfully!!!";
        return error;
    }

}
